import requests
import xlsxwriter
import time


def creatlist(item):
    temp = []
    temp.append(item['Prod_name'])  # 股票名称
    temp.append("=TEXT(" + item['Market_Symbol'] + ",\"000000\")")  # 股票代码
    temp.append(item['Last_px'])  # 最新价
    temp.append("=TEXT(" + str(item['Px_change_rate'] / 100) + ",\"0.00%\")")  # 跌涨幅
    temp.append(item['Business_amount'])  # 成交量
    temp.append(item['Business_balance'])  # 成交额
    temp.append("=TEXT(" + str(item['Amplitude'] / 100) + ",\"0.00%\")")  # 振幅
    temp.append("=TEXT(" + str(item['Turnover_ratio'] / 100) + ",\"0.00%\")")  # 换手率
    temp.append(item['Market_value'])  # 市值
    temp.append(item['Vol_ratio'])  # 量比
    temp.append(item['High_px'])  # 最高
    temp.append(item['Low_px'])  # 最低
    temp.append(item['Open_px'])  # 今开
    temp.append(item['Preclose_px'])  # 昨收
    return temp


try:
    abc = "https://hq.techgp.cn/rjhy-gmg-quote/api/1/stock/quotes/shszstocksort?en_hq_type_code=XSHG.ESA%2CXSHE.ESA%2CXSHG.KSH&sort_field_name=px_change_rate&sort_type=1&pageNo=1&pageSize=20"
    sum = requests.get(abc).json()['data']['Total']
    url = "https://hq.techgp.cn/rjhy-gmg-quote/api/1/stock/quotes/shszstocksort?en_hq_type_code=XSHG.ESA%2CXSHE.ESA%2CXSHG.KSH&sort_field_name=px_change_rate&sort_type=1&pageNo=1&pageSize=" + str(
        sum)
    res = requests.get(url).json()['data']['Stocks']

    timenow = time.strftime("%Y%m%d%H%M%S", time.localtime())
    workbook = xlsxwriter.Workbook("hushenstockexchange" + timenow + ".xlsx")
    worksheet = workbook.add_worksheet()

    namelist = ['股票名称', '股票代码', '最新价', '跌涨幅', '成交量', '成交额', '振幅', '换手率', '市值', '量比',
                '最高', '最低', '今开', '昨收']
    worksheet.write_row(0, 0, namelist)

    i = 1
    for item in res:
        my_list = creatlist(item)
        worksheet.write_row(i, 0, my_list)
        i = i + 1

    workbook.close()
except Exception as e:
    print(e)

finally:
    print("Program end!")